<?

class Functions
{
/**
 * sum values in array
 *
 * @param array $arr
 * @param string [optional]$index
 * @return int result
 */
  function array_sum_key( $arr, $index = null ){
    if(!is_array( $arr ) || sizeof( $arr ) < 1){
      return 0;
    }
    $ret = 0;
    foreach( $arr as $id => $data ){
      if( isset( $index )  ){
        $ret += (isset( $data[$index] )) ? $data[$index] : 0;
      }else{
        $ret += $data;
      }
    }
    return $ret;
  }

  function startApi() {
    require('./includes/dbconfig.php');
    require('./includes/api/class.api.php');
    require('./includes/api/class.charselect.php');
    require('./includes/api/class.charactername.php');
    // Set default timezone to match that of EVE API Server
    date_default_timezone_set('UTC');
    $api = new Api();
    $api->debug(true); // When testing and debugging your code – comment out once everything’s working

    //Connect to DB
    $link = mysql_connect($dbserver, $dbuser, $dbpass)
    or die('Could not connect: ' . mysql_error());
    mysql_select_db($dbase) or die('Could not select database');

    // Retrieve API keys and fetch extra details from API
    $sql    = 'SELECT * FROM `apikeys`';
    $result = mysql_query($sql);
    while(!($row = mysql_fetch_array($result,MYSQL_ASSOC)) == null)
    {
      $api->setCredentials($row['userID'],$row['apiKey']);
      $apicharsxml = $api->getCharacters();
      $apichars    = Characters::getCharacters($apicharsxml);
      if(!apichars == null)
      {
        $apiuser  = $row['userID'];
        $apipass  = $row['apiKey'];
        $charid   = $row['charID'];
        //break;
      }
    }

    // Set API full logon details for corp info access
    $api->setCredentials($apiuser, $apipass, $charid);
    return $api;
  }

  function getIndustryJobs() {
    require('dbconfig.php');
    $link = mysql_connect($dbserver, $dbuser, $dbpass)
    or die('Could not connect: ' . mysql_error());
    mysql_select_db($dbase) or die('Could not select database');
    $sql    = 'SELECT * FROM `jobsarchive` ORDER BY `timeTilEnd` DESC LIMIT 30;';
    $result = mysql_query($sql);
    $jobs = array();
    while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
      array_push($jobs, $row);
    }
    return $jobs;
  }

  function getConditionalAssets($select = '*', $whereclause = null, $orderby = null) {
    require('dbconfig.php');
    $link = mysql_connect($dbserver, $dbuser, $dbpass)
    or die('Could not connect: ' . mysql_error());
    mysql_select_db($dbase) or die('Could not select database');
    $assetsql    = 'SELECT '.$select.' FROM `assets` '.$whereclause.' '.$orderby;
    $assetresult = mysql_query($assetsql);
    $assets = array();
    while($assetrow = mysql_fetch_array($assetresult, MYSQL_ASSOC)) {
      if($assetrow['locationID'] >= 66000000 && $assetrow['locationID'] < 67000000) {
        $stationsql = "SELECT `stationName`, `solarSystemID` FROM `stations` WHERE `stationID` = ".($assetrow['locationID'] - 6000001).";";
        $stationresult = mysql_query($stationsql);
        $stationrow = mysql_fetch_array($stationresult);
        $assetrow['locationName'] = $stationrow['stationName'];
        $systemsql = "SELECT `solarSystemName` FROM `solarsystems` WHERE `solarSystemID` = ".$stationrow['solarSystemID'];
        $systemresult  = mysql_query($systemsql);
        $systemrow     = mysql_fetch_array($systemresult);
        $assetrow['solarSystemName'] = $systemrow['solarSystemName'];
      } elseif($assetrow['locationID'] < 66000000 && isset($assetrow['locationID'])) {
        $systemsql = "SELECT `solarSystemName` FROM `solarsystems` WHERE `solarSystemID` = ".$assetrow['locationID'];
        $systemresult  = mysql_query($systemsql);
        $systemrow     = mysql_fetch_array($systemresult);
        $assetrow['locationName'] = $systemrow['solarSystemName'];
      } else {
        $hangersql = "SELECT `hangerName` FROM `hangers` WHERE `hangerID` = ".$assetrow['flag'];
        $hangerresult  = mysql_query($hangersql);
        $hangerrow     = mysql_fetch_array($hangerresult);
        $assetrow['locationName'] = $hangerrow['hangerName'];
      }
      unset($stationrow);
      unset($systemrow);
      array_push($assets, $assetrow);
    }
    return $assets;
  }

  function getRequiredMaterials($typeToBuild, $activityID, $qty) {
    require('dbconfig.php');
    $link = mysql_connect($dbserver, $dbuser, $dbpass)
    or die('Could not connect: ' . mysql_error());
    mysql_select_db($dbase) or die('Could not select database');
    switch($activityID) {
      case 1:
      $bpstring         = "'%II Blueprint%'";
      $containerstring  = "= 'Office'";
      $exclusionstring  = "'%R.A.M.%' AND inv.groupID NOT BETWEEN 260 and 270";
      break;

      case 8:
      $bpstring         = "'% I Blueprint%'";
      $containerstring  = "IN ('Mobile Laboratory','Advanced Mobile Laboratory')";
      $exclusionstring  = "'%Interface%' AND inv.groupID NOT BETWEEN 260 and 270";
      break;

      default:
      $bpstring         = "'% I Blueprint%'";
      $containerstring  = "IN ('Mobile Laboratory','Advanced Mobile Laboratory')";
      $exclusionstring  = "'%R.A.M.%' AND inv.groupID NOT BETWEEN 260 and 270";
      break;
    }
    switch($typeToBuild) {
      case 0:
      $sql    = "SELECT inv.typeName as typeName, (IF(a.typeID =18,CEIL(mats.quantity * 1.5 ),mats.quantity)*".$qty.") as needed,
      IFNULL(a.quantity,0) as stock, IF(IF(a.typeID =18,CEIL(mats.quantity * 1.5 ),mats.quantity)*".$qty." - IFNULL(a.quantity,0) < 0,0,(IF(a.typeID =18,CEIL(mats.quantity * 1.5 ),mats.quantity)*".$qty." - IFNULL(a.quantity,0))) as toBuy
      FROM invtypes inv RIGHT OUTER JOIN (
        SELECT t1.requiredTypeID as reqd, SUM(t1.quantity) as quantity
        FROM typeactivitymaterials t1
        RIGHT OUTER JOIN (
          SELECT `typeID`
          FROM assets
          WHERE `typeName` LIKE ".$bpstring."
          AND `containerID` IN (
            SELECT `itemID`
            FROM `assets`
            WHERE `typeName` ".$containerstring."
            AND `containerID` IS NULL)) t2
        ON t1.typeID = t2.typeID
        WHERE activityID = ".$activityID."
        GROUP BY reqd) mats
      ON inv.typeID = mats.reqd
      LEFT OUTER JOIN (
        SELECT typeID, SUM(quantity) as quantity
        FROM assets
        WHERE ownerID = 0
        GROUP BY typeID) a
      ON a.typeID = mats.reqd
      WHERE mats.quantity > 0
      AND inv.typeName NOT LIKE ".$exclusionstring;
      $result = mysql_query($sql);
      $materials = array();
      while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
        array_push($materials,$row);
      }
      break;

      default:
      $sql    = "SELECT inv.typeName as typeName, inv.groupID as groupID, (IF(inv.groupID = 18,CEIL(a.quantity * 1.5),a.quantity))*".$qty." as needed, 
      a.damagePerJob, IFNULL(b.quantity,0) as stock,
      IF(((IF(inv.groupID = 18,CEIL(a.quantity * 1.5),a.quantity))*".$qty.") - IFNULL(b.quantity,0) < 0,0,((IF(inv.groupID = 18,CEIL(a.quantity * 1.5),a.quantity))*".$qty.") - IFNULL(b.quantity,0)) as toBuy
      FROM typeactivitymaterials a
      LEFT OUTER JOIN invtypes t1 ON t1.typeID = a.typeID
      LEFT OUTER JOIN invtypes inv ON inv.typeID = a.requiredTypeID
      LEFT OUTER JOIN (
        SELECT typeID, SUM(quantity) as quantity
        FROM assets
        WHERE ownerID = 0
        GROUP BY typeID) b
      ON b.typeID = a.requiredTypeID
      WHERE a.quantity > 0
      AND inv.groupID NOT BETWEEN 260 AND 270
      AND a.typeID = ".$typeToBuild."
      AND a.activityID = ".$activityID."
      AND inv.typeName NOT LIKE ".$exclusionstring."
      AND a.quantity > 0";
      $result = mysql_query($sql);
      $materials = array();
      while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
        array_push($materials,$row);
      }
    }
    return $materials;
  }

  function getPercentSuccess() {
    require('dbconfig.php');
    $link = mysql_connect($dbserver, $dbuser, $dbpass)
    or die('Could not connect: ' . mysql_error());
    mysql_select_db($dbase) or die('Could not select database');
    $sql = "SELECT i.typeName, COUNT(*), s.successes, (s.successes / COUNT(*))*100 as percentSuccess
      FROM jobsarchive i LEFT OUTER JOIN (
        SELECT typeName, COUNT(*) as successes, 'Success' FROM jobsarchive j
        WHERE j.`completed` = 1
        AND j.`completedStatus` = 1
        AND j.activityID = 8
        GROUP BY typeName) s ON i.typeName = s.typeName
      WHERE i.activityID = 8
      GROUP BY typeName";
    $result = mysql_query($sql);
    $percents = array();
    while($row = mysql_fetch_array($result)) {
      array_push($percents,$row);
    }
    return $percents;
  }

  function checkLogin($user, $passwd) {
    require('dbconfig.php');
    mysql_connect($dbserver, $dbuser, $dbpass)
    or die('Could not connect: ' . mysql_error());
    mysql_select_db($dbase) or die('Could not select database');
    $sql = "SELECT * FROM `apikeys`
    WHERE UCASE(`username`) = '".$user."' AND password = '".$passwd."'";
    $result = mysql_query($sql);
    if(mysql_num_rows($result) == 1) {
      $row = mysql_fetch_array($result);
    } else {
      return 0;
    }
    return $row['access'];
  }

  function registerNew($user, $passwd, $apikey, $userid, $charid) {
    require('./includes/dbconfig.php');
    mysql_connect($dbserver, $dbuser, $dbpass)
    or die('Could not connect: ' . mysql_error());
    mysql_select_db($dbase) or die('Could not select database');
    $sql = "INSERT INTO `apikeys` VALUES(".$userid.",'".$user."','".$apikey."',".$charid.",'".$passwd."',0)";
    mysql_query($sql) or die('Could not create user');
  }

  function getUsers() {
    require('./includes/dbconfig.php');
    mysql_connect($dbserver, $dbuser, $dbpass)
    or die('Could not connect: ' . mysql_error());
    mysql_select_db($dbase) or die('Could not select database');
    $sql = "SELECT * FROM `apikeys`";
    $result = mysql_query($sql);
    $users = array();
    while($row = mysql_fetch_array($result)) {
      array_push($users, $row);
    }
    return $users;
  }

  function setUserAccess($get) {
    require('./includes/dbconfig.php');
    mysql_connect($dbserver, $dbuser, $dbpass)
    or die('Could not connect: ' . mysql_error());
    mysql_select_db($dbase) or die('Could not select database');
    if(!$get['action'] == 0) {
      $sql = "UPDATE `apikeys`
      SET `access` = ".$get['action']."
      WHERE `username` = '".$get['user']."'
      AND `userID` = ".$get['userid']."
      LIMIT 1";
    } elseif($get['action'] == 0) {
      $sql = "DELETE FROM `apikeys`
      WHERE `username` = '".$get['user']."'
      AND `userID = ".$get['userid']."
      LIMIT 1";
    }
    mysql_query($sql) or die('Could not edit user');
  }
}
?>